﻿

Option Strict On
Option Explicit On 

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.types
Imports IP.Core.IPCommon

''' -----------------------------------------------------------------------------
''' Project	 : IPUserService
''' Class	 : Core.IPUserService.CStoredProc
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Thực hiện các Tiện ích. Giúp programmer thực hiện các stored procedure dễ hơn.
''' </summary>
''' <remarks>
''' <list type="bullet">
''' <list>
''' <item> add  loại dữ liệu</item>
''' <item> execute command </item>
''' </list>
''' </list>
''' </remarks>
''' <history>
''' 	[csung]	2/21/2005	Created
''' </history>
''' -----------------------------------------------------------------------------
Public Class CStoredProc
    Private m_OracleCommand As New OracleCommand
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' tạo mới một procedure
    ''' </summary>
    ''' <param name="i_strProcName">tên của procedure trong CSDL ví dụ: HT_NHOM_NGUOI_SU_DUNG.gran_right_to_usergroup</param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[csung]	2/21/2005	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub New(ByVal i_strProcName As String)
        m_OracleCommand.CommandText = i_strProcName
        m_OracleCommand.CommandType = CommandType.StoredProcedure
    End Sub
    Public Function addNullVarcharInputParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(ip_name, DBNull.Value)
        v_oracle_param.OracleDbType = OracleDbType.Varchar2
        Return v_oracle_param
    End Function

    Public Function addNullDecimalInputParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(ip_name, DBNull.Value)
        v_oracle_param.OracleDbType = OracleDbType.Decimal
        Return v_oracle_param
    End Function

    Public Function addNullDateInputParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(ip_name, DBNull.Value)
        v_oracle_param.OracleDbType = OracleDbType.Date

        Return v_oracle_param
    End Function

    Public Function addDecimalInputParam(ByVal ip_name As String, _
                                         ByVal ip_value As Decimal) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDecimalInputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    Public Function addRefCursorOutputParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
              m_OracleCommand.Parameters.Add(ip_name, OracleDbType.RefCursor, _
               DBNull.Value, ParameterDirection.Output)
        Return v_oracle_param
    End Function

    Public Function addVarcharInputParam(ByVal ip_name As String, _
                                             ByVal ip_value As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getVarcharInputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    Public Function addDatetimeInputParam(ByVal ip_name As String, _
                                         ByVal ip_value As DateTime) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDateTimeInputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    Public Function addDatetimeReturnParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDecimalOutputParam(ip_name, Nothing))
        v_oracle_param.Direction = ParameterDirection.ReturnValue
        Return v_oracle_param
    End Function

    Public Sub ExecuteCommand(ByVal i_UsObject As US_Object)
        m_OracleCommand.BindByName = True
        i_UsObject.ExecCommand(Me.m_OracleCommand)
    End Sub

    Public Function addDecimalOutputParam(ByVal ip_name As String, _
                                       ByVal ip_value As Decimal) As OracleParameter

        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDecimalOutputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    Public Function addDecimalReturnParam(ByVal ip_name As String) As OracleParameter

        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDecimalOutputParam(ip_name, Nothing))
        v_oracle_param.Direction = ParameterDirection.ReturnValue
        Return v_oracle_param
    End Function

    Public Function addVarcharOutputParam(ByVal ip_name As String, _
                                             ByVal ip_value As Object) As OracleParameter

        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getVarcharOutputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    Public Function addVarcharReturnParam(ByVal ip_name As String) As OracleParameter
        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getVarcharOutputParam(ip_name, Nothing))
        v_oracle_param.Direction = ParameterDirection.ReturnValue
        Return v_oracle_param
    End Function

    Public Function addDatetimeOutputParam(ByVal ip_name As String, _
                                         ByVal ip_value As DateTime) As OracleParameter

        Dim v_oracle_param As OracleParameter = _
            m_OracleCommand.Parameters.Add(CDBUtils.getDateTimeOutputParam(ip_name, ip_value))
        Return v_oracle_param
    End Function

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Chức năng này khi chuyển sang Oracle chưa hoạt động.
    ''' </summary>
    ''' <param name="i_us"></param>
    ''' <param name="i_ds"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[csung]	2/22/2005	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub fillDataSetByStoredProcedure(ByVal i_us As US_Object, ByVal i_ds As DataSet)
        Debug.Assert(Not (i_ds Is Nothing), "DS chua khoi tao")
        Debug.Assert(Not (i_us Is Nothing), "US chua khoi tao")
        Me.ExecuteCommand(i_us)
        Dim v_refcur As OracleRefCursor
        Dim v_para As OracleParameter
        For Each v_para In Me.m_OracleCommand.Parameters
            If v_para.Direction = ParameterDirection.Output And _
               v_para.OracleDbType = OracleDbType.RefCursor Then
                v_refcur = CType(v_para.Value, OracleRefCursor)
            End If
        Next
        'OracleRefCursor v_refcur = (OracleRefCursor)v_refcursor_param.Value;   
        If Not v_refcur Is Nothing Then
            Dim v_da As New OracleDataAdapter("", i_us.get_connection())
            v_da.Fill(i_ds.Tables(0), v_refcur)
        Else
            Debug.Assert(False, "Kiem tra lai ma nguon - tund")
        End If
    End Sub

    Public Function get_command() As OracleCommand
        Return Me.m_OracleCommand
    End Function
End Class
